﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace RISMOTAXDP.EmailService.MailProcess
{
    /// <summary>
    /// Newsletters 处理对象
    /// </summary>
    public class MailNewslettersProcess
    {

        private const string OpCode = "C1";

        private const string NewsletterTitle_String = "法规新闻速递";

        #region SQL
        /// <summary>
        /// SQL检查源数据
        /// </summary>
        private const string SQL_SELECT_SOURCE_Newsletters = @" SELECT id,Title,Address,Content,Type,DateSent FROM _TBL_P_QueuingMails where type='C1' ";
        /// <summary>
        /// SQL删除源数据
        /// </summary>
        private const string SQL_REMOVE_SOURCE_Newsletters = @" Delete _TBL_P_QueuingMails where type='C1' and id in ({0}) ";

        /// <summary>
        /// SQL检查是否有未完成的NewsLetters
        /// </summary>
        private const string SQL_SELECT_INCOMPLETE_Newsletters = @" SELECT ID,Title,Content,TypeCode,CreateDate,PushStatus From TBL_MailNewsletters where PushStatus = 0 order by CreateDate ";

        /// <summary>
        /// SQL新增Newsletters
        /// </summary>
        private const string SQL_INSERT_Newsletters = @" insert TBL_MailNewsletters 
(ID,Title,Content,TypeCode,CreateDate,PushStatus) 
values(@ID,@Title,@Content,@TypeCode,@CreateDate,0) ";

        /// <summary>
        /// SQL更新Newsletters的处理状态为完成
        /// </summary>
        private const string SQL_UPDATE_Newsletters_STATUS_COMPLETE = @"UPDATE TBL_MailNewsletters set PushStatus = 1 where ID=@ID";

        /// <summary>
        /// SQL获得有效订阅状态的收件人地址列表
        /// </summary>
        private const string SQL_SELECT_Recipients = @"SELECT MailAddress,SubscribeType,HasSubscribed,Name,ForumAccount,Catalog1,Catalog2
from TBL_P_Subscription
where HasSubscribed = 1 and Catalog1 =1 ";

        /// <summary>
        /// SQL检查指定指定收件人的NewsletterMail是否存在
        /// </summary>
        private const string SQL_ExistsNewsletterMail = @" SELECT COUNT(linkID) from TBL_MailNewslettersLink
 where NewsletterID= @NewsletterID and MailAddress = @MailAddress ";

        /// <summary>
        /// SQL保存邮件
        /// </summary>
        private const string SQL_SAVE_MAIL = @"insert TBL_MailQueueProcess
(Mail_ID,OperationCode,MailSubject,MailRcpt,MailContent,MailCreateDate,MailStatus)
values(@MailID,@OperationCode,@MailSubject,@MailRcpt,@MailContent,@MailCreateDate,0)";

        /// <summary>
        /// SQL保存NewsletterMailLink
        /// </summary>
        private const string SQL_SAVE_NewsletterMailLink = @"insert TBL_MailNewslettersLink
(NewsletterID,MailID,MailAddress)
values(@NewsletterID,@MailID,@MailAddress)";

        #endregion



        /// <summary>
        /// 邮件模板
        /// </summary>
        private string m_MailTemplateContent = string.Empty;

        private SqlConnection m_Conn = null;

        /// <summary>
        /// 法规条款列表
        /// </summary>
        private List<LawProvision> LawProvisions = new List<LawProvision>();
  
        /// <summary>
        /// 未完成的Newsletters
        /// </summary>
        List<Newsletter> InCompleteNewsletters = new List<Newsletter>();

        /// <summary>
        /// 收件人列表
        /// </summary>
        List<Recipient> RecipientsLists = new List<Recipient>();


        /// <summary>
        /// 构造
        /// </summary>
        public MailNewslettersProcess()
        {
            //初始化


            LawProvisions.Clear();

            InCompleteNewsletters.Clear();

        }

        /// <summary>
        /// 业务代码
        /// </summary>
        /// <returns></returns>
        private static string GetMailOPCode()
        {
            return OpCode;
        }

        /// <summary>
        /// 业务处理
        /// </summary>
        /// <param name="conn"></param>
        public void Process(SqlConnection conn)
        {
            //获取或更新模板内容
            GetMailTemplateContent();

            m_Conn = conn;

            InCompleteNewslettersProcess();

            CheckSourceData();


        }

        /// <summary>
        /// 处理未完成的Newsletters
        /// </summary>
        protected void InCompleteNewslettersProcess()
        {

            DataSet InCompleteNewslettersDS = null;

            //检查是否有未完成的Newsletters
            try
            {
                InCompleteNewslettersDS = SQLHelper.ExecuteDataset(m_Conn, CommandType.Text, SQL_SELECT_INCOMPLETE_Newsletters);
            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000002", ex);
            }

            if (InCompleteNewslettersDS == null)
            {
                return;
            }

            if (InCompleteNewslettersDS.Tables[0].Rows.Count == 0)
            {
                return;
            }

            //获取未完成的Newsletters列表
            for (int i = 0; i < InCompleteNewslettersDS.Tables[0].Rows.Count; i++)
            {
                DataRow dataRow = InCompleteNewslettersDS.Tables[0].Rows[i];

                Newsletter Newsletter =  GetNewsletterByDataRow(dataRow);

                InCompleteNewsletters.Add(Newsletter);
            }

            //如果存在未完成的，则进行根据Newsletter内容创建相关订阅收件人的邮件，完成后更新Newsletter的状态为完成
            if (InCompleteNewsletters.Count > 0)
            {
                foreach (Newsletter Newsletter in InCompleteNewsletters)
                {
                    //处理NewsletterMail
                    ProcessNewsletterMail(Newsletter);

                    //更新Newsletter状态为完成
                    UpdateNewsletterStatus(Newsletter);
                }
            }


        }



        /// <summary>
        /// 检查源数据
        /// </summary>
        /// <returns></returns>
        protected bool CheckSourceData()
        {
            //检查源数据列表中是否有新发布的法规
            DataSet ds = null;



            try
            {
                ds = SQLHelper.ExecuteDataset(m_Conn, CommandType.Text, SQL_SELECT_SOURCE_Newsletters);


            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000001", ex);
                return false;
            }

            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count != 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dataRow = ds.Tables[0].Rows[i];

                        int t_SourceID = int.Parse(dataRow["ID"].ToString());
                        int t_LawID = int.Parse(dataRow["Content"].ToString());

                        LawProvision _LawProvision = new LawProvision();
                        _LawProvision = GetLawProvision(t_SourceID, t_LawID);

                        if (_LawProvision != null)
                        {
                            LawProvisions.Add(_LawProvision);
                        }
                    }

                    CreateNewsletter();
                }
            }


            return true;
        }

        /// <summary>
        /// 移除源数据
        /// </summary>
        protected void RemoveSource()
        {
            string IDs = string.Empty;

            foreach (LawProvision _LawProvision in LawProvisions)
            {
                IDs = IDs + _LawProvision.sourceID.ToString() + ",";
            }

            if (IDs.Length > 1)
            {
                string _char = IDs.Substring((IDs.Length -1),1);
                if (_char == ",")
                {
                    IDs = IDs.Substring(0, (IDs.Length - 1));
                }
            }

            //SqlParameter[] sParms = { new SqlParameter("@IDs", IDs) };

            string strSQL = string.Format(SQL_REMOVE_SOURCE_Newsletters, IDs);


            SQLHelper.ExecuteNonQuery(m_Conn, CommandType.Text, strSQL);

        }


        #region Newsletter

        private Newsletter GetNewsletterByDataRow(DataRow dataRow)
        {
            Newsletter _Newsletter = new Newsletter();

            _Newsletter.ID = new Guid(dataRow["ID"].ToString());
            _Newsletter.Title = dataRow["Title"].ToString();
            _Newsletter.Content = dataRow["Content"].ToString();
            _Newsletter.TypeCode = dataRow["TypeCode"].ToString();
            _Newsletter.CreateDateTime = (DateTime)dataRow["CreateDate"];
            _Newsletter.Status = Convert.ToInt32(dataRow["PushStatus"]);

           return _Newsletter;
        }

        /// <summary>
        /// 创建Newsletter
        /// </summary>
        private void CreateNewsletter()
        {
            if (LawProvisions.Count == 0)
            {
                return;
            }

            if (string.IsNullOrEmpty(m_MailTemplateContent))
            {
                return;
            }

            StringBuilder sbNewsletter = new StringBuilder();

            sbNewsletter.Append(m_MailTemplateContent);

            //开始分析模板标签
            Match m;

            //分析<templateRepeatList></templateRepeatList>标签
            m = MailTemplateObject.regex_RepeatList.Match(m_MailTemplateContent);

            //未找到
            if (!m.Success)
            {
                return;
            }


            //当有一个的时候
            //包含标签
            string s_CodeContent = m.Groups[0].ToString();
            //不包含标签名
            string s_NotCodeContent = m.Groups[1].ToString();

            string d_Content = string.Empty;

            foreach (LawProvision _LawProvision in LawProvisions)
            {
                string t_Content = s_NotCodeContent;

                //开始替换
                t_Content = t_Content.Replace(MailTemplateObject.LawTitle, _LawProvision.LawTitle);
                t_Content = t_Content.Replace(MailTemplateObject.LawRefNo, _LawProvision.LawRefNo);
                t_Content = t_Content.Replace(MailTemplateObject.LawID, _LawProvision.LawID);
                t_Content = t_Content.Replace(MailTemplateObject.LawDate, _LawProvision.IssuedDate);

                d_Content = d_Content + t_Content;

            }
            //根据模板内容产生内容
            sbNewsletter.Replace(s_CodeContent, d_Content);

            //保存NewsLetters
            Guid NewsletterID = Guid.NewGuid();

            Newsletter Newsletter = new Newsletter();
            Newsletter.ID = NewsletterID;
            Newsletter.Title = NewsletterTitle_String;
            Newsletter.Content = sbNewsletter.ToString();
            Newsletter.TypeCode = GetMailOPCode();
            Newsletter.CreateDateTime = DateTime.Now;
            Newsletter.Status = 0;

            if (SaveNewsletter(Newsletter))
            {
                //保存成功，开始移除源数据
                RemoveSource();

            }

            //处理NewsletterMail
            ProcessNewsletterMail(Newsletter);

            //更新Newsletter状态为完成
            UpdateNewsletterStatus(NewsletterID);



        }

        /// <summary>
        /// 更新Newsletter状态为完成
        /// </summary>
        /// <param name="newsletter"></param>
        private void UpdateNewsletterStatus(Newsletter newsletter)
        {
            UpdateNewsletterStatus(newsletter.ID);
        }

        /// <summary>
        /// 更新Newsletter状态为完成
        /// </summary>
        /// <param name="newsletterID"></param>
        private void UpdateNewsletterStatus(Guid newsletterID)
        {
            SqlParameter[] sParms = { new SqlParameter("@ID", newsletterID) };

            try
            {
                SQLHelper.ExecuteNonQuery(m_Conn, CommandType.Text, SQL_UPDATE_Newsletters_STATUS_COMPLETE, sParms);
            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000005", ex);
            }
        }

        /// <summary>
        /// 保存Newsletter
        /// </summary>
        /// <param name="newsletter"></param>
        /// <returns></returns>
        private bool SaveNewsletter(Newsletter newsletter)
        {
            return SaveNewsletter(newsletter.ID, newsletter.Title, newsletter.Content, newsletter.TypeCode, newsletter.CreateDateTime, newsletter.Status);
        }

        /// <summary>
        /// 保存Newsletter
        /// </summary>
        /// <param name="newsletterID"></param>
        /// <param name="newsletterTitle"></param>
        /// <param name="newsletterContent"></param>
        /// <param name="typeCode"></param>
        /// <param name="createDateTime"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        private bool SaveNewsletter(Guid newsletterID, string newsletterTitle,  string newsletterContent, string typeCode, DateTime createDateTime, int status)
        {
            SqlParameter[] sParms = { new SqlParameter("@ID",newsletterID),
                                    new SqlParameter("@Title",newsletterTitle),
                                    new SqlParameter("@Content",newsletterContent),
                                    new SqlParameter("@TypeCode",typeCode),
                                    new SqlParameter("@CreateDate",createDateTime)};

            try
            {
                SQLHelper.ExecuteNonQuery(m_Conn, CommandType.Text, SQL_INSERT_Newsletters, sParms);
            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000003", ex);
                return false;
            }

            return true;
        }

        #endregion

        #region NewsletterMail

        /// <summary>
        /// 根据newsletterID处理NewsletterMail邮件
        /// 检查newsletterID所对应的NewsletterMail是否已创建，是否送入待发送队列
        /// 给指定收件人创建NewsletterMail
        /// </summary>
        /// <param name="newsletterID"></param>
        protected void ProcessNewsletterMail(Newsletter newsletter)
        {

            //获取最新订阅者地址列表
            RecipientsLists.Clear();
            RecipientsLists = GetRecipientsList();

            //开始生成NewsletterMail并投递到待发送队列中
            foreach (Recipient recipient in RecipientsLists)
            {
                //生成NewsletterMail前检查当前的收件人是否已生成NewsletterMail
                if (ExistsNewsletterMail(recipient,newsletter))
                {
                    //已存在，跳过
                    break;
                }


                //创建待发送邮件
                CreateNewsletterMail(recipient, newsletter);

            }

        }

        /// <summary>
        /// 创建指定收件人的NewsletterMail
        /// </summary>
        /// <param name="recipient"></param>
        /// <param name="newsletter"></param>
        private void CreateNewsletterMail(Recipient recipient, Newsletter newsletter)
        {
            bool saveMail = true;

            //将生成内容的邮件加入TBL_MailQueueProcess表中
 

            Guid guidMailID = Guid.NewGuid();


            saveMail = SaveNewsletterMail(guidMailID, GetMailOPCode(), newsletter.Title, recipient.Email, newsletter.Content, newsletter.CreateDateTime);


            if (saveMail)
            {
                SaveNewsletterMailLink(guidMailID, newsletter.ID, recipient.Email);
            }

        }

        /// <summary>
        /// 保存NewsletterMail
        /// </summary>
        /// <param name="mailID"></param>
        /// <param name="operationCode"></param>
        /// <param name="mailSubject"></param>
        /// <param name="mailRcpt"></param>
        /// <param name="mailContent"></param>
        /// <param name="mailCreateDate"></param>
        private bool SaveNewsletterMail(Guid mailID, string operationCode, string mailSubject, string mailRcpt, string mailContent, DateTime mailCreateDate)
        {
            //将生成内容的邮件加入TBL_MailQueueProcess表中
            SqlParameter[] sParms = { new SqlParameter("@MailID", mailID),
                                                    new SqlParameter("@OperationCode", operationCode),
                                                    new SqlParameter("@MailSubject", mailSubject),
                                                    new SqlParameter("@MailRcpt", mailRcpt),
                                                    new SqlParameter("@MailContent", mailContent),
                                                    new SqlParameter("@MailCreateDate", mailCreateDate)
                        
                                                };
            try
            {
                SQLHelper.ExecuteNonQuery(m_Conn, CommandType.Text, SQL_SAVE_MAIL, sParms);
            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000004", ex);
                return false;
            }
            return true;

        }

        /// <summary>
        /// 保存NewsletterMailLink
        /// </summary>
        /// <param name="mailID"></param>
        /// <param name="newsletterID"></param>
        /// <param name="mailAddress"></param>
        /// <returns></returns>
        private bool SaveNewsletterMailLink(Guid mailID, Guid newsletterID, string mailAddress)
        {
            //将生成内容的邮件加入TBL_MailQueueProcess表中
            SqlParameter[] sParms = { new SqlParameter("@MailID", mailID),
                                                    new SqlParameter("@NewsletterID", newsletterID),
                                                    new SqlParameter("@MailAddress", mailAddress)                        
                                                };
            try
            {
                SQLHelper.ExecuteNonQuery(m_Conn, CommandType.Text, SQL_SAVE_NewsletterMailLink, sParms);
            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000006", ex);
                return false;
            }
            return true;
        }


        /// <summary>
        /// 检查指定收件人的指定NewsletterMail
        /// </summary>
        /// <param name="recipient"></param>
        /// <param name="newsletter"></param>
        /// <returns>true 存在 false 不存在</returns>
        private bool ExistsNewsletterMail(Recipient recipient, Newsletter newsletter)
        {
            SqlParameter[] sParms = { new SqlParameter("@MailAddress",recipient.Email),
                                      new SqlParameter("@NewsletterID",newsletter.ID)
                                    };
        
           int rowsAffected = (int)SQLHelper.ExecuteScalar(m_Conn, CommandType.Text, SQL_ExistsNewsletterMail,sParms);

           if (rowsAffected > 0)
           {
               return true;
           }

          return false;
        }

        #endregion

        #region Recipients

        private List<Recipient> GetRecipientsList()
        {
            List<Recipient> Recipients = new List<Recipient>();

            DataSet ds = null;

            try
            {
                ds = SQLHelper.ExecuteDataset(m_Conn, CommandType.Text, SQL_SELECT_Recipients);

            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E87000007", ex);
                return null;
            }

            if (ds == null) { return null; }
            if (ds.Tables[0].Rows.Count == 0) { return null; }

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dataRow = ds.Tables[0].Rows[i];

                Recipient recipient = new Recipient();

                recipient.Email = dataRow["MailAddress"].ToString();

                Recipients.Add(recipient);
            }
            
            return Recipients;
        }

        #endregion


        #region helper
        /// <summary>
        /// 获取邮件模板
        /// </summary>
        private void GetMailTemplateContent()
        {
            string MailTemplate = System.IO.Path.Combine(GlobalObject.MailTemplatePath, GlobalObject.DataBaseInfo.MailOperationList[GetMailOPCode()].MailTemplate);

            try
            {
                using (System.IO.StreamReader streamReader = new System.IO.StreamReader(MailTemplate, Encoding.UTF8))
                {

                    streamReader.ReadLine();
                    m_MailTemplateContent = streamReader.ReadToEnd();

                }

            }
            catch (Exception ex)
            {
                GlobalObject.WriteOperationLog("E81000001", ex, GlobalObject.DataBaseInfo.MailOperationList[GetMailOPCode()].OpCode);

            }
        }

        /// <summary>
        /// 获取法规
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="lawID"></param>
        /// <returns></returns>
        private LawProvision GetLawProvision(int sourceID, int lawID)
        {
            LawProvision _LawProvision = new LawProvision();

            SqlParameter[] sParms = { new SqlParameter("@LawID",lawID)
            };
            DataSet ds = new DataSet();

            StringBuilder commandText_Query = new StringBuilder();

            commandText_Query.Append(" SELECT * FROM vwLawProvisions_List ");
            commandText_Query.Append(" where ID = @LawID");

            ds = SQLHelper.ExecuteDataset(m_Conn, CommandType.Text, commandText_Query.ToString(), sParms);

            //读取查询结果并组装成列表
            if (ds.Tables[0].Rows.Count != 0)
            {
                DataRow dataRow = ds.Tables[0].Rows[0];

                _LawProvision.sourceID = sourceID;
                _LawProvision.LawID = dataRow["ID"].ToString();
                _LawProvision.LawTitle = dataRow["LawTitle"].ToString();
                _LawProvision.LawRefNo = dataRow["LawRefNo"].ToString();
                _LawProvision.LawBasicCategory = dataRow["BasicCategoryName"].ToString();
                _LawProvision.LawTransactionType = dataRow["TransactionTypeName"].ToString();
                _LawProvision.LawIndustry = dataRow["IndustryName"].ToString();
                _LawProvision.IssuedDate = GlobalObject.FormatShortDateTime(Convert.ToDateTime(dataRow["IssuedDate"]));
                _LawProvision.IssuingAuthority = GetLawProvisionIssuingAuthority(Convert.ToInt32(_LawProvision.LawID));//dataRow["IssuingAuthority"].ToString();  

            }
            else
            {
                _LawProvision = null;
            }
            return _LawProvision;

        }



        #region 获得某法规的发文机构

        private string GetLawProvisionIssuingAuthority(int LawID)
        {
            StringBuilder IssuingAuthority = new StringBuilder();
            StringBuilder commandText_Query = new StringBuilder();

            SqlParameter[] sParms = { new SqlParameter("@LawID",LawID)
            };
            DataSet ds = new DataSet();

            commandText_Query.Append(" SELECT dbo.TBL_P_ProvisionAgents.ProvisionID, ");
            commandText_Query.Append(" t_IssuingAuthority.IssuingAuthority");
            commandText_Query.Append(" FROM dbo.TBL_P_ProvisionAgents ");
            commandText_Query.Append(" LEFT OUTER JOIN");
            commandText_Query.Append(" ( select groupID,[key],value as IssuingAuthority from dbo.TBL_P_Dictionary ");
            commandText_Query.Append("    WHERE   TBL_P_Dictionary.GroupID = 'Agent' ) t_IssuingAuthority");
            commandText_Query.Append(" ON dbo.TBL_P_ProvisionAgents.AgentID = t_IssuingAuthority.[Key]");
            commandText_Query.Append(" where 1=1");
            commandText_Query.Append(" and provisionID = @LawID");

            ds = SQLHelper.ExecuteDataset(m_Conn,CommandType.Text, commandText_Query.ToString(), sParms);

            //读取查询结果并组装成列表
            if (ds.Tables[0].Rows.Count != 0)
            {
                foreach (DataRow dataRow in ds.Tables[0].Rows)
                {
                    IssuingAuthority.AppendFormat("{0}; ", dataRow["IssuingAuthority"].ToString());
                }

                IssuingAuthority.Remove(IssuingAuthority.Length - 2, 2);

            }

            return IssuingAuthority.ToString();

        }

        #endregion


        #endregion
    }


    /// <summary>
    /// Newsletter
    /// </summary>
    public class Newsletter
    {
        public Newsletter() { }

        public Guid ID { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public string TypeCode { get; set; }
        public int Status { get; set; }
        public DateTime CreateDateTime { get; set; }

    }

    /// <summary>
    /// LawProvision
    /// </summary>
    public class LawProvision
    {
        public LawProvision() { }

        #region
        public int sourceID { get; set; }
        public string LawID { get; set; }
        public string LawTitle { get; set; }
        public string LawRefNo { get; set; }
        public string LawBasicCategory { get; set; }
        public string LawTransactionType { get; set; }
        public string LawIndustry { get; set; }
        public string IssuedDate { get; set; }
        public string IssuingAuthority { get; set; }

        #endregion

    }


    /// <summary>
    /// 收件人
    /// </summary>
    public class Recipient
    {
        public Recipient() { }

        public string Email { get; set; }

    }


    /// <summary>
    /// 邮件列表 Mailing list
    /// </summary>
    public class Mailinglist
    {
        public Mailinglist() { }

        public string Email { get; set; }
    }

}
